"""
保单 CRUD 操作
"""
from typing import Optional, List
from tortoise.exceptions import DoesNotExist
from ..models.policy import Policy
from ..models.user import User
from ..schemas.policy import PolicyCreate, PolicyUpdate
from ..core.exceptions import UserNotFoundError
from datetime import date


class PolicyCRUD:
    """保单 CRUD 操作类"""

    @staticmethod
    async def create_policy(policy_data: PolicyCreate) -> Policy:
        """创建保单"""
        # 检查保单号是否已存在
        existing_policy = await Policy.filter(policy_number=policy_data.policy_number).first()
        if existing_policy:
            raise ValueError("保单号已存在")
        
        # 检查销售员是否存在
        salesman = await User.filter(job_number=policy_data.salesman_job_number).first()
        if not salesman:
            raise UserNotFoundError(f"销售员工号 {policy_data.salesman_job_number} 不存在")
        
        # 创建新保单
        policy = await Policy.create(
            policy_number=policy_data.policy_number,
            applicant_name=policy_data.applicant_name,
            insured_name=policy_data.insured_name,
            insured_count=policy_data.insured_count,
            insured_date=policy_data.insured_date,
            start_date=policy_data.start_date,
            end_date=policy_data.end_date,
            premium=policy_data.premium,
            occupation=policy_data.occupation,
            salesman=salesman,  # 直接传递用户对象，让Tortoise处理外键
            company_id=policy_data.company_id,
            product_id=policy_data.product_id,
            risk_type_id=policy_data.risk_type_id,
            issuance_type_id=policy_data.issuance_type_id,
            file_path=policy_data.file_path
        )
        return policy

    @staticmethod
    async def get_policy_by_number(policy_number: str) -> Policy:
        """根据保单号获取保单"""
        try:
            policy = await Policy.get(policy_number=policy_number)
            return policy
        except DoesNotExist:
            raise ValueError(f"保单号 {policy_number} 不存在")

    @staticmethod
    async def get_policies(skip: int = 0, limit: int = 100) -> List[Policy]:
        """获取保单列表"""
        return await Policy.all().offset(skip).limit(limit)

    @staticmethod
    async def get_policies_with_salesman_info(skip: int = 0, limit: int = 100) -> List[dict]:
        """获取保单列表（包含销售员信息）"""
        policies = await Policy.all().select_related('salesman__role').offset(skip).limit(limit)
        
        # 构造返回数据，包含销售员信息
        result = []
        for policy in policies:
            policy_dict = {
                "policy_number": policy.policy_number,
                "applicant_name": policy.applicant_name,
                "insured_name": policy.insured_name,
                "insured_count": policy.insured_count,
                "insured_date": policy.insured_date,
                "start_date": policy.start_date,
                "end_date": policy.end_date,
                "premium": policy.premium,
                "occupation": policy.occupation,
                "salesman_job_number": getattr(policy, 'salesman_id', None),
                "company_id": policy.company_id,
                "product_id": policy.product_id,
                "risk_type_id": policy.risk_type_id,
                "issuance_type_id": policy.issuance_type_id,
                "file_path": policy.file_path,
                "created_at": policy.created_at,
                "updated_at": policy.updated_at,
                "salesman_name": policy.salesman.username if policy.salesman else None,
                "salesman_role_name": policy.salesman.role.role_name if policy.salesman and policy.salesman.role else None,
            }
            result.append(policy_dict)
        
        return result

    @staticmethod
    async def get_policy_detail(policy_number: str) -> dict:
        """根据保单号获取保单详情（包含销售员信息）"""
        try:
            policy = await Policy.get(policy_number=policy_number).select_related('salesman__role')

            return {
                "policy_number": policy.policy_number,
                "applicant_name": policy.applicant_name,
                "insured_name": policy.insured_name,
                "insured_count": policy.insured_count,
                "insured_date": policy.insured_date,
                "start_date": policy.start_date,
                "end_date": policy.end_date,
                "premium": policy.premium,
                "occupation": policy.occupation,
                "salesman_job_number": getattr(policy, 'salesman_id', None),
                "company_id": policy.company_id,
                "product_id": policy.product_id,
                "risk_type_id": policy.risk_type_id,
                "issuance_type_id": policy.issuance_type_id,
                "file_path": policy.file_path,
                "created_at": policy.created_at,
                "updated_at": policy.updated_at,
                "salesman_name": policy.salesman.username if policy.salesman else None,
                "salesman_role_name": policy.salesman.role.role_name if policy.salesman and policy.salesman.role else None,
            }
        except DoesNotExist:
            raise ValueError(f"保单号 {policy_number} 不存在")

    @staticmethod
    async def update_policy(policy_number: str, policy_data: PolicyUpdate) -> Policy:
        """更新保单信息"""
        policy = await PolicyCRUD.get_policy_by_number(policy_number)
        
        # 如果更新销售员，检查是否存在
        if policy_data.salesman_job_number:
            salesman = await User.filter(job_number=policy_data.salesman_job_number).first()
            if not salesman:
                raise UserNotFoundError(f"销售员工号 {policy_data.salesman_job_number} 不存在")
            # 更新销售员关联
            policy.salesman = salesman
        
        # 获取更新数据并排除销售员字段（已单独处理）
        update_data = policy_data.model_dump(exclude_unset=True, exclude={'salesman_job_number'})
        
        # 更新其他字段
        if update_data:
            await policy.update_from_dict(update_data)
        
        await policy.save()
        return policy

    @staticmethod
    async def delete_policy(policy_number: str) -> bool:
        """删除保单"""
        policy = await PolicyCRUD.get_policy_by_number(policy_number)
        await policy.delete()
        return True

    @staticmethod
    async def get_policies_by_salesman(salesman_job_number: str, skip: int = 0, limit: int = 100) -> List[Policy]:
        """根据销售员获取保单列表"""
        return await Policy.filter(salesman_id=salesman_job_number).offset(skip).limit(limit)

    @staticmethod
    async def count_policies() -> int:
        """统计保单数量"""
        return await Policy.all().count()

    @staticmethod
    async def search_policies(
        applicant_name: Optional[str] = None,
        insured_name: Optional[str] = None,
        company_id: Optional[str] = None,
        product_id: Optional[str] = None,
        salesman_job_number: Optional[List[str]] = None,
        risk_type_id: Optional[str] = None,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
        skip: int = 0,
        limit: int = 100
    ) -> List[Policy]:
        """根据条件搜索保单"""
        query = Policy.all()

        if applicant_name:
            query = query.filter(applicant_name__icontains=applicant_name)
        if insured_name:
            query = query.filter(insured_name__icontains=insured_name)
        if company_id:
            query = query.filter(company_id=company_id)
        if product_id:
            query = query.filter(product_id=product_id)
        if salesman_job_number:
            query = query.filter(salesman_id__in=salesman_job_number)
        if risk_type_id:
            query = query.filter(risk_type_id=risk_type_id)
        if start_date:
            query = query.filter(start_date__gte=start_date)
        if end_date:
            query = query.filter(end_date__lte=end_date)
        
        return await query.offset(skip).limit(limit)


# 创建全局实例
policy_crud = PolicyCRUD()